<%
On Error resume next
Response.Addheader "Content-Type","text/html; charset=gb2312" 

set conn = server.CreateObject("ADODB.Connection") '创建连接变量
set rs1 = server.CreateObject("ADODB.Recordset") '创建连接变量
set rs = server.CreateObject("ADODB.Recordset") '创建连接变量
conn.Open "DRIVER={SQL Server};Server=(local);UID=sa;pwd=123456;DATABASE=HPTsoft" '通过dsn语句打开连接

'rs.Open "DROP TABLE AA部门补贴标准",conn 删除数据表

'rs.Open "DELETE FROM AA部门补贴标准",conn 清空数据表


sqlstr = "SELECT TOP(1) * FROM AA部门补贴标准"
rs1.Open sqlstr,conn

If Err.Number = -2147217865 Then 
	'如果报错-2147217865就创建空表
	sqlstr = "CREATE TABLE [dbo].[AA部门补贴标准]( "
	sqlstr = sqlstr & "	[ID] [int] NOT NULL,"
	sqlstr = sqlstr & "	[部门名称] [varchar](30) NULL,"
	sqlstr = sqlstr & "	[早餐补贴金额] [decimal](10, 2) NULL,"
	sqlstr = sqlstr & "	[午餐补贴金额] [decimal](10, 2) NULL,"
	sqlstr = sqlstr & "	[晚餐补贴金额] [decimal](10, 2) NULL,"
	sqlstr = sqlstr & "	[夜宵补贴金额] [decimal](10, 2) NULL"
	sqlstr = sqlstr & ") ON [PRIMARY]"
	rs1.Open sqlstr,conn 
	Err.Number = 0
	'response.write "{""data"":[],""err"":{""code"":1" & Err.Number & ",""Source"":""" & Err.Source & """,""Description"":""" & Err.Description & """}"
  'response.End
	' 关闭记录集和连接 
 	'rs_bz.Close 
'	conn.Close 
 	'Set rs_bz = Nothing 
'	Set conn = Nothing 	
End If


'新数据插入到dept表
sqlstr = "INSERT INTO AA部门补贴标准 ( ID, 部门名称, 早餐补贴金额, 午餐补贴金额, 晚餐补贴金额,  夜宵补贴金额) "
sqlstr = sqlstr & "SELECT ID, Name ,0,0,0,0 "
sqlstr = sqlstr & " FROM DepartInfo "
sqlstr = sqlstr & " WHERE NOT EXISTS ("
sqlstr = sqlstr & "      SELECT 1 "
sqlstr = sqlstr & "      FROM AA部门补贴标准 "
sqlstr = sqlstr & "      WHERE AA部门补贴标准.ID = DepartInfo.ID "
sqlstr = sqlstr & ");"
rs.Open sqlstr,conn 


If Err.Number <> 0 Then
	'有数据时会报3705?
	response.write "{""data"":[],""err"":{""code7"":" & Err.Number & ",""Source"":""" & Err.Source & """,""Description"":""" & Err.Description & """}"
	response.End
		' 关闭记录集和连接 
	rs1.Close 
	rs.Close 
	conn.Close 
	Set rs1 = Nothing 
	Set rs = Nothing 
	Set conn = Nothing 	
End If


'rs.Close 

'sqlstr = "SELECT ID, Name AS 部门名称 ,  CAST(0 AS DECIMAL(10, 2)) AS 早餐补贴金额 ,"
'sqlstr = sqlstr & " CAST(0 AS DECIMAL(10, 2)) AS 午餐补贴金额 ,"
'sqlstr = sqlstr & " CAST(0 AS DECIMAL(10, 2)) AS 晚餐补贴金额 ,"
'sqlstr = sqlstr & " CAST(0 AS DECIMAL(10, 2)) AS 夜宵补贴金额 "
'sqlstr = sqlstr & " INTO AA部门补贴标准 FROM DepartInfo;"
'rs.Open sqlstr,conn 


sqlstr = "SELECT ID,部门名称,早餐补贴金额,午餐补贴金额,晚餐补贴金额,夜宵补贴金额 FROM AA部门补贴标准"
rs.Open sqlstr,conn '查询部门补贴标准

response.write "{""data"":["
do while not rs.eof
	response.write "{""ID""" & ":""" & rs("ID") & ""","
	response.write """部门名称""" & ":""" & rs("部门名称") & ""","
	response.write """早餐补贴金额""" & ":""" & rs("早餐补贴金额") & ""","
	response.write """午餐补贴金额""" & ":""" & rs("午餐补贴金额") & ""","
	response.write """晚餐补贴金额""" & ":""" & rs("晚餐补贴金额") & ""","
	response.write """夜宵补贴金额""" & ":""" & rs("夜宵补贴金额") & """}"
	rs.movenext
		If not rs.eof Then
				response.write ","
		End If
loop
response.write "],"
response.write """err"":{""code"":" & Err.Number & ",""Source"":""" & Err.Source & """,""Description"":""" & Err.Description & """}"
response.write "}"
' 关闭记录集和连接 
rs1.Close
rs.Close 
conn.Close 
Set rs1 = Nothing 
Set rs = Nothing 
Set conn = Nothing
response.End 

%>